{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e3769158",
   "metadata": {},
   "source": [
    "# 任务一  统计订单时间时长"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ceba9b2",
   "metadata": {},
   "source": [
    "- 试用饼图实现**统计订单时间时长**，数据源'Taxi_sz.csv'\n",
    "- 代码需要注解"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5fffd11c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "65d1266d",
   "metadata": {},
   "source": [
    "# 任务二 统计不同时段的速度"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "553770e9",
   "metadata": {},
   "source": [
    "**统计不同时段的速度。**数据源--清洗后的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42b35e53",
   "metadata": {},
   "outputs": [],
   "source": [
    "def differentSpeed():\n",
    "\n",
    "    df = pd.read_csv(r'./data/Taxi_sz_data.csv')\n",
    "    df = df[-(df['Speed'] == 0)]\n",
    "\n",
    "    # 假设 df 包含以时间格式表示的 'Stime' 列\n",
    "    df['Stime'] = pd.to_datetime(df['Stime'], format='%H:%M:%S')\n",
    "\n",
    "    # 为不同时间段创建区间\n",
    "    bins = pd.cut(df['Stime'].dt.hour, bins=[8, 10, 12, 14, 16, 18, 20, 22, 24], labels=[\n",
    "                  '8-10', '10-12', '12-14', '14-16', '16-18', '18-20', '20-22', '22-24'])\n",
    "\n",
    "    # 计算每个时间段的最大、平均和最小速度\n",
    "    grouped_df = df.groupby(bins)['Speed'].agg(\n",
    "        ['max', 'mean', 'min']).reset_index()\n",
    "    # 创建新的 DataFrame 以适应 plot 函数\n",
    "    data = pd.DataFrame({\n",
    "        '时间段': ['8-10', '10-12', '12-14', '14-16', '16-18', '18-20', '20-22', '22-24'],\n",
    "        '最快速度': grouped_df['max'].tolist(),\n",
    "        '平均速度': grouped_df['mean'].tolist(),\n",
    "        '最小速度': grouped_df['min'].tolist()\n",
    "    })\n",
    "    # 绘图\n",
    "    data.set_index('时间段').plot(kind='bar', rot=0)\n",
    "    plt.xlabel('时间段', fontproperties='simhei')\n",
    "    plt.ylabel('速度', fontproperties='simhei')\n",
    "    plt.title('不同时间段的速度统计', fontproperties='simhei')\n",
    "    plt.rcParams['font.family'] = 'simHei'\n",
    "    plt.legend()\n",
    "    plt.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9599bc3d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bb22929",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "dbbfa436",
   "metadata": {},
   "source": [
    "# 任务三  不同时间段载有乘客的数量"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb0377c3",
   "metadata": {},
   "source": [
    "**不同时间段载有乘客的数量**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "944a8ea2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据源---Taxi_sz.csv\n",
    "# 提取小时值\n",
    "data_se['Hour'] = data_se['Stime'].str.slice(0, 2)\n",
    "# 转换数据结构\n",
    "hourCount = data_se.groupby(\n",
    "    'Hour')['VehicleNum'].count().rename('count').reset_index()\n",
    "# 分析绘图\n",
    "mpl.rcParams[\"font.sans-serif\"] = ['SimHei']\n",
    "plt.xlabel('时间/h')\n",
    "plt.ylabel('车数/辆')\n",
    "plt.title('各个时间段行驶的有乘客的车辆数目')\n",
    "#plt.ylim(0, 1000)\n",
    "#plt.plot(hourCount['Hour'], hourCount['count'])\n",
    "plt.plot(hourCount['Hour'], hourCount['count'])\n",
    "plt.grid(True)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4ed4535",
   "metadata": {},
   "source": [
    "# 任务四--统计不同车辆的平均行驶速度"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "594b78d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def isVehicle(VehicleNum):\n",
    "    taxiDataVehicleNum = data_clean['VehicleNum'].unique()\n",
    "    # display(taxiDataVehicleNum)\n",
    "    return VehicleNum in taxiDataVehicleNum\n",
    "\n",
    "\n",
    "def meanSpeed(VehicleNum):\n",
    "    i = VehicleNum\n",
    "    while isVehicle(i) == False:\n",
    "        print('车辆牌号不存在，请重新输入（共有3次机会）！')\n",
    "\n",
    "        i = eval(input())\n",
    "        if i == 3:\n",
    "            return\n",
    "        else:\n",
    "            continue\n",
    "\n",
    "    data_clean['Hour'] = data_clean['Stime'].str.slice(0, 2)\n",
    "    taxiDataByVehicleNum = data_clean[(data_clean['VehicleNum'] == i)]\n",
    "    meanSpeed = taxiDataByVehicleNum.groupby(\n",
    "        'Hour')['Speed'].mean().rename('vehicleSpeedMean').reset_index()\n",
    "    # display(meanSpeed)\n",
    "    # 开始画图\n",
    "    #mpl.rcParams[\"font.sans-serif\"] = ['SimHei']\n",
    "    #mpl.rcParams[\"axes.unicode_minus\"] = False\n",
    "    plt.xlabel(\"时间/小时\")\n",
    "    plt.ylabel(\"平均速度\")\n",
    "    plt.title(\"车牌号为:\"+VehicleNum+\"出租车的平均速度\")\n",
    "    plt.plot(meanSpeed['Hour'], meanSpeed['vehicleSpeedMean'])\n",
    "    plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3a77e51",
   "metadata": {},
   "outputs": [],
   "source": [
    "#调用方法，完成需求---理解代码，添加注解"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3a5e385",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54ae4791",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0769d95a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "eec3b60c",
   "metadata": {},
   "source": [
    "# 任务五--展示出租车一天第一次载客时的连续行驶路径"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "668f5e77",
   "metadata": {},
   "source": [
    "**任务--展示出租车一天第一次载客时的连续行驶路径**\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8e3f8bb",
   "metadata": {},
   "source": [
    "需完善：需要判断车牌号的正确性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0056bdcd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导出经纬度   ---需要判断车牌号的正确性\n",
    "import pandas as pd\n",
    "\n",
    "taxi = pd.read_csv('./data/Taxi_sz_data.csv') \n",
    "num = eval(input('请输入需要导出经纬度数据的车牌号：'))\n",
    "\n",
    "taxi_num = taxi[taxi['VehicleNum'] == num].reset_index()\n",
    "\n",
    "for i in range(len(taxi_num)):\n",
    "    time = (pd.to_datetime(taxi_num['Stime'][i]\n",
    "                           ) - pd.to_datetime('00:00:00')).seconds\n",
    "    taxi_num['Stime'][i] = time\n",
    "taxi_time = taxi_num.sort_values(by=['VehicleNum', 'Stime']).reset_index()\n",
    "#print(taxi_time)\n",
    "taxi_loc = taxi_time[['Lng', 'Lat']]\n",
    "values = taxi_loc.values\n",
    "with open(str(num) + \"taxi_line.txt\", mode=\"a\", encoding=\"utf-8\") as f:   # 保存在/data该如何实现？？？\n",
    "    f.seek(0)\n",
    "    f.truncate()\n",
    "    for i in range(len(taxi_loc)):\n",
    "        try:\n",
    "            if taxi_time['OpenStatus'][i] == 1 and taxi_time['OpenStatus'][i+1] == 1:\n",
    "                if values[i][0] == values[i-1][0] and values[i][1] == values[i-1][1]:\n",
    "                    continue\n",
    "                lng = float(values[i][0])\n",
    "                lat = float(values[i][1])\n",
    "                if lng == \"\" or lat == \"\":\n",
    "                    continue\n",
    "                dic = {'lat': lat, 'lng': lng}\n",
    "                f.write(str(dic))\n",
    "                if i != len(taxi_loc) - 1:\n",
    "                    f.write(\",\\n\")\n",
    "            elif taxi_time['OpenStatus'][i] == 1 and taxi_time['OpenStatus'][i+1] == 0:\n",
    "                if values[i][0] == values[i-1][0] and values[i][1] == values[i-1][1]:\n",
    "                    continue\n",
    "                lng = float(values[i][0])\n",
    "                lat = float(values[i][1])\n",
    "                if lng == \"\" or lat == \"\":\n",
    "                    continue\n",
    "                dic = {'lat': lat, 'lng': lng}\n",
    "                f.write(str(dic))\n",
    "                if i != len(taxi_loc) - 1:\n",
    "                    f.write(\",\\n\")\n",
    "                break\n",
    "        except:\n",
    "            continue\n",
    "print('已成功导出！\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c576d6f",
   "metadata": {},
   "source": [
    "# 任务--绘制乘客上车、下车地点的热力图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d95be7a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导出经纬度--开始数据\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "c = pd.read_csv(\"./data/Taxi_sz.csv\", encoding=\"utf-8\", low_memory=False)\n",
    "num = c.groupby([\"SLng\", \"SLat\"])[\"VehicleNum\"].count()\n",
    "index1 = num.index\n",
    "values1 = num.values\n",
    "num1 = len(num)\n",
    "for it in range(num1):\n",
    "    try:\n",
    "        lng = float(index1[it][0])\n",
    "        lat = float(index1[it][1])\n",
    "        count = int(values1[it])\n",
    "        if lng == \"\" or lat == \"\":\n",
    "            continue\n",
    "        dic = {'lat': lat, 'lng': lng, 'count': count}\n",
    "        if it == 0:\n",
    "            with open(\"./data/start.txt\", mode=\"w\", encoding=\"utf-8\") as f:  # a可以续写，w则是清空再写\n",
    "                dic = str(dic)\n",
    "                f.write(dic + \",\")\n",
    "                f.write(\"\\n\")   # write()  read()\n",
    "        else: \n",
    "            with open(\"./data/start.txt\", mode=\"a\", encoding=\"utf-8\") as f:\n",
    "                if it == num1 - 1:\n",
    "                    dic = str(dic)\n",
    "                    f.write(dic)\n",
    "                else:\n",
    "                    dic = str(dic)\n",
    "                    f.write(dic + \",\")\n",
    "                    f.write(\"\\n\")\n",
    "    except:\n",
    "        continue\n",
    "print(\"导出成功\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3d672a4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23bb3b8a",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%time\n",
    "# 导出经纬度--结束数据\n",
    "import pandas as pd\n",
    "\n",
    "c = pd.read_csv(\"./data/Taxi_sz.csv\", encoding=\"utf-8\", low_memory=False)\n",
    "num = c.groupby([\"SLng\", \"SLat\"])[\"VehicleNum\"].count()\n",
    "index1 = num.index\n",
    "values1 = num.values\n",
    "num1 = len(num)\n",
    "for it in range(num1):\n",
    "    try:\n",
    "        lng = float(index1[it][0])\n",
    "        lat = float(index1[it][1])\n",
    "        count = int(values1[it])\n",
    "        if lng == \"\" or lat == \"\":\n",
    "            continue\n",
    "        dic = {'lat': lat, 'lng': lng, 'count': count}\n",
    "        if it == 0:\n",
    "            with open(\"./data/end.txt\", mode=\"w\", encoding=\"utf-8\") as f:  # a可以续写，w则是清空再写\n",
    "                dic = str(dic)\n",
    "                f.write(dic + \",\")\n",
    "                f.write(\"\\n\")\n",
    "        else:\n",
    "            with open(\"./data/end.txt\", mode=\"a\", encoding=\"utf-8\") as f:\n",
    "                if it == num1 - 1:\n",
    "                    dic = str(dic)\n",
    "                    f.write(dic)\n",
    "                else:\n",
    "                    dic = str(dic)\n",
    "                    f.write(dic + \",\")\n",
    "                    f.write(\"\\n\")\n",
    "    except:\n",
    "        continue\n",
    "print(\"导出成功\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71fa951e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f40d7895",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "0ce25cb8",
   "metadata": {},
   "source": [
    "# 任务六--车(载客时)的各个平均速度范围内的出租车辆的分布情况¶"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfd745b6",
   "metadata": {},
   "source": [
    "**按照平均速度范围统计位于该范围内的出租车辆数的分布情况,并用（统计图）展示出来**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "de77f98c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2126e831",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f1eb2ce",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03289e3e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "bf76af58",
   "metadata": {},
   "source": [
    "# 作业"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2334ef41",
   "metadata": {},
   "source": [
    "**汽车贷款违约的数据分析**。 数据源--data.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4edea7de",
   "metadata": {},
   "outputs": [],
   "source": [
    "试着获取数据、分析数据，按实际情况完成数据的处理与可视化"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5b37e65",
   "metadata": {},
   "source": [
    "'申请者ID','帐户号','是否违约','汽车购买时间','汽车制造商','曾经破产标识','五年内信用不良事件数量','全部帐户数量',\n",
    "           '账号存续月份数','开户帐户数量','信用卡数量','信用卡欠款余额','信用卡授信额度','信用卡额度使用比例',\n",
    "           'FICO打分','汽车购买金额','建议售价','分期付款的首次交款','贷款期限','贷款金额','贷款金额/售价','月均收入','行驶里程','是否二手车','样本权重'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ec42659f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as  pd \n",
    "data = pd.read_csv('./data/data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3adc146c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>account_number</th>\n",
       "      <th>bad_ind</th>\n",
       "      <th>vehicle_year</th>\n",
       "      <th>vehicle_make</th>\n",
       "      <th>bankruptcy_ind</th>\n",
       "      <th>tot_derog</th>\n",
       "      <th>tot_tr</th>\n",
       "      <th>age_oldest_tr</th>\n",
       "      <th>tot_open_tr</th>\n",
       "      <th>...</th>\n",
       "      <th>purch_price</th>\n",
       "      <th>msrp</th>\n",
       "      <th>down_pyt</th>\n",
       "      <th>loan_term</th>\n",
       "      <th>loan_amt</th>\n",
       "      <th>ltv</th>\n",
       "      <th>tot_income</th>\n",
       "      <th>veh_mileage</th>\n",
       "      <th>used_ind</th>\n",
       "      <th>weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2314049</td>\n",
       "      <td>11613</td>\n",
       "      <td>1</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>FORD</td>\n",
       "      <td>N</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>17200.00</td>\n",
       "      <td>17350.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>36</td>\n",
       "      <td>17200.00</td>\n",
       "      <td>99.0</td>\n",
       "      <td>6550.00</td>\n",
       "      <td>24000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63539</td>\n",
       "      <td>13449</td>\n",
       "      <td>0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>DAEWOO</td>\n",
       "      <td>N</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>240.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>...</td>\n",
       "      <td>19588.54</td>\n",
       "      <td>19788.0</td>\n",
       "      <td>683.54</td>\n",
       "      <td>60</td>\n",
       "      <td>19588.54</td>\n",
       "      <td>99.0</td>\n",
       "      <td>4666.67</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7328510</td>\n",
       "      <td>14323</td>\n",
       "      <td>1</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>PLYMOUTH</td>\n",
       "      <td>N</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>13595.00</td>\n",
       "      <td>11450.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>60</td>\n",
       "      <td>10500.00</td>\n",
       "      <td>92.0</td>\n",
       "      <td>2000.00</td>\n",
       "      <td>19600.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8725187</td>\n",
       "      <td>15359</td>\n",
       "      <td>1</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>FORD</td>\n",
       "      <td>N</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>12999.00</td>\n",
       "      <td>12100.0</td>\n",
       "      <td>3099.00</td>\n",
       "      <td>60</td>\n",
       "      <td>10800.00</td>\n",
       "      <td>118.0</td>\n",
       "      <td>1500.00</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4275127</td>\n",
       "      <td>15812</td>\n",
       "      <td>0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>TOYOTA</td>\n",
       "      <td>N</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>26328.04</td>\n",
       "      <td>22024.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>60</td>\n",
       "      <td>26328.04</td>\n",
       "      <td>122.0</td>\n",
       "      <td>4144.00</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>8712513</td>\n",
       "      <td>16979</td>\n",
       "      <td>0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>DODGE</td>\n",
       "      <td>Y</td>\n",
       "      <td>2.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>136.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>26272.72</td>\n",
       "      <td>26375.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>36</td>\n",
       "      <td>26272.72</td>\n",
       "      <td>100.0</td>\n",
       "      <td>5400.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2063896</td>\n",
       "      <td>17842</td>\n",
       "      <td>0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>INFINITI</td>\n",
       "      <td>N</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>339.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>31069.00</td>\n",
       "      <td>30519.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>36</td>\n",
       "      <td>9550.00</td>\n",
       "      <td>32.0</td>\n",
       "      <td>5416.67</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>598458</td>\n",
       "      <td>19715</td>\n",
       "      <td>0</td>\n",
       "      <td>1994.0</td>\n",
       "      <td>BUICK</td>\n",
       "      <td>N</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>261.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>9600.00</td>\n",
       "      <td>8900.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>54</td>\n",
       "      <td>8600.00</td>\n",
       "      <td>98.0</td>\n",
       "      <td>1560.00</td>\n",
       "      <td>77267.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1526052</td>\n",
       "      <td>23924</td>\n",
       "      <td>1</td>\n",
       "      <td>1994.0</td>\n",
       "      <td>MITT</td>\n",
       "      <td>N</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>213.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6700.00</td>\n",
       "      <td>6350.0</td>\n",
       "      <td>500.00</td>\n",
       "      <td>42</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>139.0</td>\n",
       "      <td>2416.67</td>\n",
       "      <td>40000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8073975</td>\n",
       "      <td>24866</td>\n",
       "      <td>0</td>\n",
       "      <td>1999.0</td>\n",
       "      <td>CHEV</td>\n",
       "      <td>Y</td>\n",
       "      <td>11.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>15543.00</td>\n",
       "      <td>15100.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>60</td>\n",
       "      <td>14543.00</td>\n",
       "      <td>102.0</td>\n",
       "      <td>2933.33</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   application_id  account_number  bad_ind  vehicle_year vehicle_make  \\\n",
       "0         2314049           11613        1        1998.0         FORD   \n",
       "1           63539           13449        0        2000.0       DAEWOO   \n",
       "2         7328510           14323        1        1998.0     PLYMOUTH   \n",
       "3         8725187           15359        1        1997.0         FORD   \n",
       "4         4275127           15812        0        2000.0       TOYOTA   \n",
       "5         8712513           16979        0        2000.0        DODGE   \n",
       "6         2063896           17842        0        2000.0     INFINITI   \n",
       "7          598458           19715        0        1994.0        BUICK   \n",
       "8         1526052           23924        1        1994.0         MITT   \n",
       "9         8073975           24866        0        1999.0         CHEV   \n",
       "\n",
       "  bankruptcy_ind  tot_derog  tot_tr  age_oldest_tr  tot_open_tr  ...  \\\n",
       "0              N        7.0     9.0           64.0          2.0  ...   \n",
       "1              N        0.0    21.0          240.0         11.0  ...   \n",
       "2              N        7.0    10.0           60.0          NaN  ...   \n",
       "3              N        3.0    10.0           35.0          5.0  ...   \n",
       "4              N        0.0    10.0          104.0          2.0  ...   \n",
       "5              Y        2.0    15.0          136.0          4.0  ...   \n",
       "6              N        0.0    13.0          339.0          4.0  ...   \n",
       "7              N        0.0     2.0          261.0          NaN  ...   \n",
       "8              N        2.0    13.0          213.0          3.0  ...   \n",
       "9              Y       11.0    20.0          178.0          NaN  ...   \n",
       "\n",
       "   purch_price     msrp  down_pyt  loan_term  loan_amt    ltv  tot_income  \\\n",
       "0     17200.00  17350.0      0.00         36  17200.00   99.0     6550.00   \n",
       "1     19588.54  19788.0    683.54         60  19588.54   99.0     4666.67   \n",
       "2     13595.00  11450.0      0.00         60  10500.00   92.0     2000.00   \n",
       "3     12999.00  12100.0   3099.00         60  10800.00  118.0     1500.00   \n",
       "4     26328.04  22024.0      0.00         60  26328.04  122.0     4144.00   \n",
       "5     26272.72  26375.0      0.00         36  26272.72  100.0     5400.00   \n",
       "6     31069.00  30519.0      0.00         36   9550.00   32.0     5416.67   \n",
       "7      9600.00   8900.0      0.00         54   8600.00   98.0     1560.00   \n",
       "8      6700.00   6350.0    500.00         42   6800.00  139.0     2416.67   \n",
       "9     15543.00  15100.0      0.00         60  14543.00  102.0     2933.33   \n",
       "\n",
       "   veh_mileage  used_ind  weight  \n",
       "0      24000.0         1    1.00  \n",
       "1         22.0         0    4.75  \n",
       "2      19600.0         1    1.00  \n",
       "3      10000.0         1    1.00  \n",
       "4         14.0         0    4.75  \n",
       "5          1.0         0    4.75  \n",
       "6        500.0         0    4.75  \n",
       "7      77267.0         1    4.75  \n",
       "8      40000.0         1    1.00  \n",
       "9       6000.0         1    4.75  \n",
       "\n",
       "[10 rows x 25 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0e13d45",
   "metadata": {},
   "source": [
    "<font size=4 color=red>主要是参考其数据特征工程的处理方式</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a226e4e1",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'os' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[3], line 10\u001b[0m\n\u001b[0;32m      8\u001b[0m path \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m/Users/gray/Desktop\u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m      9\u001b[0m \u001b[38;5;66;03m# 注意文件夹路径，双斜杠\u001b[39;00m\n\u001b[1;32m---> 10\u001b[0m path_name \u001b[38;5;241m=\u001b[39m os\u001b[38;5;241m.\u001b[39mpath\u001b[38;5;241m.\u001b[39mjoin(path, \u001b[38;5;124m'\u001b[39m\u001b[38;5;124mdata.csv\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m     11\u001b[0m \u001b[38;5;66;03m# 注意，os.path.join函数，执行路径拼接\u001b[39;00m\n\u001b[0;32m     13\u001b[0m data \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mread_csv(path_name)\n",
      "\u001b[1;31mNameError\u001b[0m: name 'os' is not defined"
     ]
    }
   ],
   "source": [
    "# 读入数据\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.tree import DecisionTreeClassifier, plot_tree\n",
    "import matplotlib.pyplot as plt\n",
    "import sklearn.metrics as metrics\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.model_selection import train_test_split\n",
    "path = '/Users/gray/Desktop'\n",
    "# 注意文件夹路径，双斜杠\n",
    "path_name = os.path.join(path, 'data.csv')\n",
    "# 注意，os.path.join函数，执行路径拼接\n",
    "\n",
    "data = pd.read_csv(path_name)\n",
    "#data = pd.read_csv('data.csv')\n",
    "# 查看样本形状，样本条数，样本属性数量\n",
    "data.shape\n",
    "# 查看数据前5条数据\n",
    "data.head()\n",
    "# 查看数据大概情况\n",
    "data.describe().T\n",
    "# 观察并通过duplicated方检查发现现application_id account_number都是样本的唯一编号且二者同值，取其一即可\n",
    "data.loc[:, ['application_id', 'account_number']].duplicated().sum()\n",
    "data.dtypes\n",
    "# 分别划分X变量与Y变量\n",
    "x_list = ['vehicle_year', 'vehicle_make', 'bankruptcy_ind', 'tot_derog', 'tot_tr',\n",
    "          'age_oldest_tr', 'tot_open_tr', 'tot_rev_tr', 'tot_rev_debt', 'tot_rev_line', 'rev_util', 'fico_score',\n",
    "          'purch_price',\n",
    "          'msrp', 'down_pyt', 'loan_term', 'loan_amt', 'ltv', 'tot_income', 'veh_mileage', 'used_ind', 'weight']\n",
    "data_x = data.loc[:, x_list]\n",
    "data_y = data.loc[:, 'bad_ind']\n",
    "\n",
    "\n",
    "# 查看Y变量值的分布 正负样本数量\n",
    "data_y.value_counts()\n",
    "# 真对X变量进行数据探索\n",
    "pd.set_option('display.max_columns', None)\n",
    "data_x_des = data_x.describe(include='all').T\n",
    "data_x_des\n",
    "# 将变量中文名与变量一一对应，方便后续进行查看\n",
    "all_list = list(data.columns)\n",
    "cn_label = ['申请者ID', '帐户号', '是否违约', '汽车购买时间', '汽车制造商', '曾经破产标识', '五年内信用不良事件数量', '全部帐户数量',\n",
    "            '账号存续月份数', '开户帐户数量', '信用卡数量', '信用卡欠款余额', '信用卡授信额度', '信用卡额度使用比例',\n",
    "            'FICO打分', '汽车购买金额', '建议售价', '分期付款的首次交款', '贷款期限', '贷款金额', '贷款金额/售价', '月均收入', '行驶里程', '是否二手车', '样本权重'\n",
    "            ]\n",
    "label_dict = {}\n",
    "for i in range(len(all_list)):\n",
    "    label_dict[all_list[i]] = cn_label[i]\n",
    "# 将label拼接到X变量\n",
    "label_series = pd.Series(label_dict)\n",
    "data_x_des['label'] = label_series\n",
    "data_x.isnull().sum()\n",
    "\n",
    "###########################\n",
    "# 查看月均收入的总体分布情况和缺失值情况\n",
    "data_x['tot_income'].value_counts(dropna=False)\n",
    "data_x['tot_income'].describe().T\n",
    "data_x['tot_income'].isnull().sum()\n",
    "# 查看每项的违约情况\n",
    "data_y.groupby(data_x['tot_income']).agg(['count', 'mean'])\n",
    "# 缺失值采用中位数填充\n",
    "data_x['tot_income'] = data_x['tot_income'].fillna(\n",
    "    data_x['tot_income'].median())  #众，中位数\n",
    "# 盖帽处理\n",
    "q25 = data_x['tot_income'].quantile(0.25)\n",
    "q75 = data_x['tot_income'].quantile(0.75)\n",
    "max_qz = q75+1.5*(q75-q25)\n",
    "sum(data_x['tot_income'] > max_qz)\n",
    "# 359 #存在259个样本的取值超过理论极大值，进行盖帽\n",
    "\n",
    "temp_series = data_x['tot_income'] > max_qz\n",
    "data_x.loc[temp_series, 'tot_income'] = max_qz\n",
    "data_x['tot_income'].describe()\n",
    "\n",
    "# 对信用卡授信额度进行预处理\n",
    "data_x['tot_rev_line'].value_counts(dropna=False)\n",
    "data_x['tot_rev_line'].describe().T\n",
    "data_x['tot_rev_line'].isnull().sum()\n",
    "# 查看每项的违约情况\n",
    "data_y.groupby(data_x['tot_rev_line']).agg(['count', 'mean'])\n",
    "data_x['tot_rev_line1'] = data_x['tot_rev_line'].fillna('unknown')\n",
    "data_y.groupby(data_x['tot_rev_line1']).agg(['count', 'mean'])\n",
    "# 盖帽处理\n",
    "q25 = data_x['tot_rev_line'].quantile(0.25)\n",
    "q75 = data_x['tot_rev_line'].quantile(0.75)\n",
    "max_qz = q75+1.5*(q75-q25)\n",
    "sum(data_x['tot_rev_line'] > max_qz)\n",
    "# 259 #存在259个样本的取值超过理论极大值，进行盖帽\n",
    "\n",
    "temp_series = data_x['tot_rev_line'] > max_qz\n",
    "data_x.loc[temp_series, 'tot_rev_line'] = max_qz\n",
    "data_x['tot_rev_line'].describe()\n",
    "# 将数据分箱 用999999填充缺失值\n",
    "data_x['tot_rev_line_fx'] = pd.qcut(\n",
    "    data_x['tot_rev_line'], 10, labels=False, duplicates='drop')\n",
    "data_x['tot_rev_line_fx'] = data_x['tot_rev_line_fx'].fillna(999999)\n",
    "\n",
    "# 查看不同码值对应的违约率情况\n",
    "data_y.groupby(data_x['tot_rev_line_fx']).agg(['count', 'mean'])\n",
    "\n",
    "# 汽车制造年份预处理\n",
    "# 产看其缺失值情况\n",
    "data_x.loc[:, 'vehicle_year'].value_counts().sort_index()\n",
    "data_x['vehicle_year'].isnull().sum()\n",
    "data_y.groupby(data_x['vehicle_year']).agg(['count', 'mean'])\n",
    "\n",
    "# 填充缺失值\n",
    "data_x.loc[:, 'vehicle_year'][data_x.loc[:,\n",
    "                                         'vehicle_year'].isin([0, 9999])] = np.nan\n",
    "data_x['vehicle_year'] = data_x['vehicle_year'].fillna(\n",
    "    data_x['vehicle_year'].median())\n",
    "\n",
    "# 对破产标示进行预处理\n",
    "data_x['bankruptcy_ind'].value_counts(dropna=False)\n",
    "data_x['bankruptcy_ind1'] = data_x['bankruptcy_ind'].fillna('unknown')\n",
    "data_y.groupby(data_x['bankruptcy_ind1']).agg(['count', 'mean'])\n",
    "\n",
    "# 重新划分X与Y变量\n",
    "x_var_list = ['tot_derog', 'tot_tr', 'age_oldest_tr', 'tot_open_tr', 'tot_rev_tr', 'tot_rev_debt', 'tot_rev_line', \n",
    "              'rev_util', 'fico_score', 'purch_price',\n",
    "              'msrp', 'down_pyt', 'loan_term', 'loan_amt', 'ltv', 'tot_income', 'veh_mileage', 'used_ind']\n",
    "data_x = data.loc[:, x_var_list]\n",
    "data_y = data.loc[:, 'bad_ind']\n",
    "# 用中位数填充缺失值\n",
    "temp = data_x.median()\n",
    "temp_dict = {}\n",
    "for i in range(len(list(temp.index))):\n",
    "    temp_dict[list(temp.index)[i]] = list(temp.values)[i]\n",
    "\n",
    "data_x_fill = data_x.fillna(temp_dict)\n",
    "# 使用train_test_split划分训练集与测试集\n",
    "train_x, test_x, train_y, test_y = train_test_split(\n",
    "    data_x_fill, data_y, test_size=0.25, random_state=12345)\n",
    "\n",
    "# 引入线性回归工具包\n",
    "linear = LinearRegression()\n",
    "# 模型训练\n",
    "model = linear.fit(train_x, train_y)\n",
    "# 查看相关系数\n",
    "linear.intercept_\n",
    "linear.coef_\n",
    "# 排序得出权重最大的几个变量\n",
    "var_coef = pd.DataFrame()\n",
    "var_coef['var'] = x_var_list\n",
    "var_coef['coef'] = linear.coef_\n",
    "var_coef.sort_values(by='coef', ascending=False)\n",
    "\n",
    "# clf.predict_proba，计算每个样本的预测概率，预测y=0，y=1，提取y=1的一列数据\n",
    "# 注意，roc_curve函数，返回假正率、真正率、门槛值\n",
    "# 基于测试集,进行auc模型评估\n",
    "fpr, tpr, th = metrics.roc_curve(test_y, linear.predict(test_x))\n",
    "metrics.auc(fpr, tpr)\n",
    "# 绘制ROC曲线\n",
    "plt.figure(figsize=[8, 8])\n",
    "plt.plot(fpr, tpr, color='b')\n",
    "plt.plot([0, 1], [0, 1], color='r', alpha=.5, linestyle='--')\n",
    "plt.show()\n",
    "\n",
    "\n",
    "# 使用决策树模型进行数据分析\n",
    "tree = DecisionTreeClassifier()\n",
    "# 模型训练\n",
    "tree.fit(train_x, train_y)\n",
    "# 查看树的深度\n",
    "len(np.unique(tree.apply(train_x)))\n",
    "# 查看模型训练效果\n",
    "fpr, tpr, th = metrics.roc_curve(\n",
    "    test_y, tree.predict_proba(test_x.values)[:, 1])\n",
    "metrics.auc(fpr, tpr)\n",
    "\n",
    "# 调整决策树参数重新构建决策树 重新设置树的最大深度和叶节点大小\n",
    "tree2 = DecisionTreeClassifier(max_depth=20, min_samples_leaf=100)\n",
    "tree2.fit(train_x, train_y)\n",
    "# 查看树的深度\n",
    "len(np.unique(tree2.apply(train_x)))\n",
    "\n",
    "# 查看auc评估指标\n",
    "fpr, tpr, th = metrics.roc_curve(\n",
    "    test_y, tree2.predict_proba(test_x.values)[:, 1])\n",
    "metrics.auc(fpr, tpr)\n",
    "# 查看决策树结构\n",
    "plt.figure(figsize=[16, 10])\n",
    "plot_tree(tree2, filled=True)\n",
    "# plot_tree函数，绘制决策树的整体结构\n",
    "plt.show()\n",
    "# 绘制ROC曲线\n",
    "plt.figure(figsize=[8, 8])\n",
    "plt.plot(fpr, tpr, color='b')\n",
    "plt.plot([0, 1], [0, 1], color='r', alpha=.5, linestyle='--')\n",
    "plt.show()\n",
    "\n",
    "# 采用随机森林模型进行分类预测\n",
    "#forest = RandomForestClassifier(n_estimators=100, max_depth=20, min_samples_leaf=100,random_state=11223)\n",
    "forest = RandomForestClassifier()\n",
    "# 模型训练\n",
    "forest.fit(train_x, train_y)\n",
    "# 查看auc评估指标\n",
    "fpr, tpr, th = metrics.roc_curve(\n",
    "    test_y, forest.predict_proba(test_x.values)[:, 1])\n",
    "metrics.auc(fpr, tpr)\n",
    "# 调参构建新的随机森林\n",
    "forest1 = RandomForestClassifier(\n",
    "    n_estimators=100, max_depth=20, min_samples_leaf=100, random_state=11223)\n",
    "# 构建新的随机森林模型\n",
    "forest1.fit(train_x, train_y)\n",
    "# 查看auc评估指标\n",
    "fpr, tpr, th = metrics.roc_curve(\n",
    "    test_y, forest1.predict_proba(test_x.values)[:, 1])\n",
    "metrics.auc(fpr, tpr)\n",
    "# 绘制ROC曲线\n",
    "plt.figure(figsize=[8, 8])\n",
    "plt.plot(fpr, tpr, color='b')\n",
    "plt.plot([0, 1], [0, 1], color='r', alpha=.5, linestyle='--')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "401b6b46",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "245.76px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
